const express = require('express');
const router = express.Router();
const rds = require('../rds');
const logger = require('../logger');
const moment = require('moment');
const mui = require('../mui');
const excel = require('exceljs');

// 退款订单查询页面
router.get('/', async (req, res) => {
    try {
        // 获取上个月第一天和今天的日期
        const today = moment();
        const firstDayOfLastMonth = moment().subtract(1, 'months').startOf('month');

        // 获取查询参数
        let {
            begin = firstDayOfLastMonth.format('YYYY-MM-DD'),
            end = today.format('YYYY-MM-DD'),
            uid = '',
            spread_uid = '',
            rep_code = '',
            real_name = '',
            user_phone = '',
            user_address = '',
            refund_type = 'weixin',
            order_id = '',
            original_order_id = '',
            min_amount = '',
            max_amount = '',
            page = 1,
            limit = 50
        } = req.query;

        // 如果是crm-sales或crm-channel角色且没有指定rep_code，则使用session中的repCODE
        if ((req.session.crmROLE === 'crm-sales' || req.session.crmROLE === 'crm-channel') )  rep_code = req.session.repCODE || 'FBD';

        // 转换参数类型
        page = parseInt(page);
        limit = parseInt(limit);
        min_amount = min_amount ? parseFloat(min_amount) : null;
        max_amount = max_amount ? parseFloat(max_amount) : null;

        // 构建查询条件
        const conditions = [];
        const params = [];

        // 时间范围条件
        const beginTimestamp = moment(begin).unix();
        const endTimestamp = moment(end).endOf('day').unix();
        conditions.push('b.add_time >= ?');
        conditions.push('b.add_time <= ?');
        params.push(beginTimestamp, endTimestamp);

        // 其他查询条件
        if (uid) {
            conditions.push('b.uid = ?');
            params.push(uid);
        }
        if (spread_uid) {
            conditions.push('u.spread_uid = ?');
            params.push(spread_uid);
        }
        if (rep_code) {
            conditions.push('o.rep_code = ?');
            params.push(rep_code);
        }
        if (real_name) {
            conditions.push('o.real_name LIKE ?');
            params.push(`%${real_name}%`);
        }
        if (user_phone) {
            conditions.push('o.user_phone LIKE ?');
            params.push(`%${user_phone}%`);
        }
        if (user_address) {
            conditions.push('o.user_address LIKE ?');
            params.push(`%${user_address}%`);
        }
        if (order_id) {
            conditions.push('b.id = ?');
            params.push(order_id);
        }
        if (original_order_id) {
            conditions.push('o.id = ?');
            params.push(original_order_id);
        }
        if (min_amount !== null) {
            conditions.push('b.number >= ?');
            params.push(min_amount);
        }
        if (max_amount !== null) {
            conditions.push('b.number <= ?');
            params.push(max_amount);
        }

        // 退款类型条件
        if (refund_type === 'weixin') {
            conditions.push('b.category = ? AND b.type = ?');
            params.push('refund_product', 'weixin_pay');
        } else if (refund_type === 'tupay') {
            conditions.push('b.category = ? AND b.type = ?');
            params.push('now_money', 'pay_product_refund');
        } else if (refund_type === 'all') {
            conditions.push('(b.category = ? AND b.type = ?) OR (b.category = ? AND b.type = ?)');
            params.push('refund_product', 'weixin_pay', 'now_money', 'pay_product_refund');
        }

        // 构建查询SQL
        const baseQuery = `
            FROM eb_user_bill b
            LEFT JOIN eb_store_order o ON b.link_id = o.id
            LEFT JOIN eb_user u ON b.uid = u.uid
            WHERE ${conditions.join(' AND ')}
        `;

        // 查询总记录数
        const countQuery = `
            SELECT COUNT(*) as total, SUM(b.number) as total_amount 
            FROM eb_user_bill b
            LEFT JOIN eb_store_order o ON b.link_id = o.id
            LEFT JOIN eb_user u ON b.uid = u.uid
            WHERE ${conditions.join(' AND ')}
        `;

        const [totalResult] = await rds.query(countQuery, params);
        const total = totalResult[0].total;
        const totalRefundAmount = parseFloat(totalResult[0].total_amount || 0);
        const totalPages = Math.ceil(total / limit);

        // 查询分页数据
        const query = `
            SELECT 
                b.id as refund_id,
                DATE_FORMAT(FROM_UNIXTIME(b.add_time), '%Y-%m-%d %H:%i:%s') as refund_time,
                o.id as order_id,
                DATE_FORMAT(FROM_UNIXTIME(o.pay_time), '%Y-%m-%d %H:%i:%s') as pay_time,
                b.uid,
                u.spread_uid as channel_id,
                o.rep_code,
                o.real_name,
                CONCAT('*******', RIGHT(o.user_phone, 4)) as user_phone,
                CONCAT(LEFT(o.user_address, GREATEST(0, CHAR_LENGTH(o.user_address) - 8)), REPEAT('*', LEAST(8, CHAR_LENGTH(o.user_address)))) as user_address,
                o.pay_price as order_amount,
                b.number as refund_amount,
                o.pay_price - COALESCE(
                    (SELECT SUM(number) 
                     FROM eb_user_bill 
                     WHERE category = 'refund_product' 
                     AND link_id = o.id), 0
                ) as remaining_balance,
                CASE 
                    WHEN b.category = 'refund_product' AND b.type = 'weixin_pay' THEN '微信退款'
                    WHEN b.category = 'now_money' AND b.type = 'pay_product_refund' THEN '图贝退款'
                    ELSE b.type 
                END as refund_type,
                b.mark as refund_remark
            FROM eb_user_bill b
            LEFT JOIN eb_store_order o ON b.link_id = o.id
            LEFT JOIN eb_user u ON b.uid = u.uid
            WHERE ${conditions.join(' AND ')}
            ORDER BY b.add_time DESC
            LIMIT ${limit} OFFSET ${(page - 1) * limit}
        `;

        const [rows] = await rds.query(query, params);

        // 生成下载链接
        const downloadLink = `
            <div class="row">
                <div class="col-12">
                    <div class="d-flex justify-content-between align-items-center">
                        <div>
                            <span>共 ${total} 条退款记录，总退款金额 ¥${totalRefundAmount.toFixed(2)}</span>
                        </div>
                        <div>
                            <a href="/apps/admin/refunds/export?begin=${begin}&end=${end}&uid=${uid}&spread_uid=${spread_uid}&refund_type=${refund_type}&rep_code=${rep_code}&real_name=${encodeURIComponent(real_name)}&user_phone=${encodeURIComponent(user_phone)}&user_address=${encodeURIComponent(user_address)}" class="btn btn-sm btn-primary">
                                <i class="fas fa-download"></i> 下载Excel
                            </a>
                        </div>
                    </div>
                </div>
            </div>
        `;

        // 计算当前页的记录范围
        const startRecord = (page - 1) * limit + 1;
        const endRecord = Math.min(page * limit, total);

        // 渲染页面
        res.render('refund/list', {
            title: '退款订单查询',
            description: '查询订单退款记录',
            rows,
            page,
            limit,
            total,
            totalPages,
            startRecord,
            endRecord,
            begin,
            end,
            uid,
            spread_uid,
            rep_code,
            real_name,
            user_phone,
            user_address,
            refund_type,
            order_id,
            original_order_id,
            downloadLink,
            paginator: mui.generatePaginator(3, totalPages, page, `/apps/admin/refunds?begin=${begin}&end=${end}&uid=${uid}&spread_uid=${spread_uid}&rep_code=${rep_code}&real_name=${real_name}&user_phone=${user_phone}&user_address=${user_address}&refund_type=${refund_type}&order_id=${order_id}&original_order_id=${original_order_id}&limit=${limit}`)
        });

    } catch (error) {
        logger.error('Error fetching refund orders:', error);
        res.status(500).render('warn', {
            title: '系统错误',
            message: '获取退款订单列表失败',
            description: error.message
        });
    }
});

// 导出退款记录
router.get('/export', async (req, res) => {
    try {
        let {
            begin = moment().subtract(1, 'months').startOf('month').format('YYYY-MM-DD'),
            end = moment().format('YYYY-MM-DD'),
            uid = '',
            spread_uid = '',
            rep_code = '',
            real_name = '',
            user_phone = '',
            user_address = '',
            refund_type = 'weixin'
        } = req.query;

        // 如果是crm-sales或crm-channel角色且没有指定rep_code，则使用session中的repCODE
        if ((req.session.crmROLE === 'crm-sales' || req.session.crmROLE === 'crm-channel') )  rep_code = req.session.repCODE || 'FBD';

        // 构建查询条件
        const conditions = [];
        const params = [];

        // 时间范围条件
        const beginTimestamp = moment(begin).unix();
        const endTimestamp = moment(end).endOf('day').unix();
        conditions.push('b.add_time >= ?');
        conditions.push('b.add_time <= ?');
        params.push(beginTimestamp, endTimestamp);

        // 其他查询条件
        if (uid) {
            conditions.push('b.uid = ?');
            params.push(uid);
        }
        if (spread_uid) {
            conditions.push('u.spread_uid = ?');
            params.push(spread_uid);
        }
        if (rep_code) {
            conditions.push('o.rep_code = ?');
            params.push(rep_code);
        }
        if (real_name) {
            conditions.push('o.real_name LIKE ?');
            params.push(`%${real_name}%`);
        }
        if (user_phone) {
            conditions.push('o.user_phone LIKE ?');
            params.push(`%${user_phone}%`);
        }
        if (user_address) {
            conditions.push('o.user_address LIKE ?');
            params.push(`%${user_address}%`);
        }

        // 退款类型条件
        if (refund_type === 'weixin') {
            conditions.push('b.category = ? AND b.type = ?');
            params.push('refund_product', 'weixin_pay');
        } else if (refund_type === 'tupay') {
            conditions.push('b.category = ? AND b.type = ?');
            params.push('now_money', 'pay_product_refund');
        } else if (refund_type === 'all') {
            conditions.push('(b.category = ? AND b.type = ?) OR (b.category = ? AND b.type = ?)');
            params.push('refund_product', 'weixin_pay', 'now_money', 'pay_product_refund');
        }

        // 查询数据
        const [rows] = await rds.query(`
            SELECT 
                b.id as refund_id,
                DATE_FORMAT(FROM_UNIXTIME(b.add_time), '%Y-%m-%d %H:%i:%s') as refund_time,
                o.id as order_id,
                DATE_FORMAT(FROM_UNIXTIME(o.pay_time), '%Y-%m-%d %H:%i:%s') as pay_time,
                b.uid,
                u.spread_uid as channel_id,
                o.rep_code,
                o.real_name,
                CONCAT('*******', RIGHT(o.user_phone, 4)) as user_phone,
                CONCAT(LEFT(o.user_address, GREATEST(0, CHAR_LENGTH(o.user_address) - 8)), REPEAT('*', LEAST(8, CHAR_LENGTH(o.user_address)))) as user_address,
                o.pay_price as order_amount,
                b.number as refund_amount,
                o.pay_price - COALESCE(
                    (SELECT SUM(number) 
                     FROM eb_user_bill 
                     WHERE category = 'refund_product' 
                     AND link_id = o.id), 0
                ) as remaining_balance,
                CASE 
                    WHEN b.category = 'refund_product' AND b.type = 'weixin_pay' THEN '微信退款'
                    WHEN b.category = 'now_money' AND b.type = 'pay_product_refund' THEN '图贝退款'
                    ELSE b.type 
                END as refund_type,
                b.mark as refund_remark
            FROM eb_user_bill b
            LEFT JOIN eb_store_order o ON b.link_id = o.id
            LEFT JOIN eb_user u ON b.uid = u.uid
            WHERE ${conditions.join(' AND ')}
            ORDER BY b.add_time DESC
        `, params);

        // 创建Excel工作簿
        const workbook = new excel.Workbook();
        const worksheet = workbook.addWorksheet('退款记录');

        // 设置列
        worksheet.columns = [
            { header: '退款流水号', key: 'refund_id', width: 15 },
            { header: '退款时间', key: 'refund_time', width: 20 },
            { header: '订单号', key: 'order_id', width: 15 },
            { header: '支付时间', key: 'pay_time', width: 20 },
            { header: '用户号', key: 'uid', width: 10 },
            { header: '渠道号', key: 'channel_id', width: 10 },
            { header: '代表代码', key: 'rep_code', width: 15 },
            { header: '用户姓名', key: 'real_name', width: 15 },
            { header: '用户电话', key: 'user_phone', width: 15 },
            { header: '用户地址', key: 'user_address', width: 30 },
            { header: '订单金额', key: 'order_amount', width: 12 },
            { header: '退款金额', key: 'refund_amount', width: 12 },
            { header: '剩余金额', key: 'remaining_balance', width: 12 },
            { header: '退款类型', key: 'refund_type', width: 12 },
            { header: '退款备注', key: 'refund_remark', width: 30 }
        ];

        // 添加数据
        worksheet.addRows(rows);

        // 设置响应头
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', `attachment; filename=refund-orders-${begin}-${end}.xlsx`);

        // 写入响应
        await workbook.xlsx.write(res);
        res.end();
    } catch (error) {
        logger.error('Error exporting refund records:', error);
        res.status(500).json({ error: error.message });
    }
});

// 退款统计
router.get('/stats', async (req, res) => {
    try {
        let {
            begin = moment().subtract(1, 'months').startOf('month').format('YYYY-MM-DD'),
            end = moment().format('YYYY-MM-DD'),
            group_by = 'date' // date, channel, rep
        } = req.query;

        const conditions = [
            'DATE_FORMAT(FROM_UNIXTIME(b.add_time), "%Y-%m-%d") >= ?',
            'DATE_FORMAT(FROM_UNIXTIME(b.add_time), "%Y-%m-%d") <= ?'
        ];
        const params = [begin, end];

        let groupField, selectFields;
        switch (group_by) {
            case 'channel':
                groupField = 'u.spread_uid';
                selectFields = `
                    u.spread_uid as group_key,
                    COALESCE(u2.real_name, '') as group_name,
                    COALESCE(u2.agency, '') as group_desc
                `;
                break;
            case 'rep':
                groupField = 'o.rep_code';
                selectFields = `
                    o.rep_code as group_key,
                    o.rep_code as group_name,
                    '' as group_desc
                `;
                break;
            default: // date
                groupField = 'DATE_FORMAT(FROM_UNIXTIME(b.add_time), "%Y-%m-%d")';
                selectFields = `
                    ${groupField} as group_key,
                    ${groupField} as group_name,
                    '' as group_desc
                `;
        }

        const [stats] = await rds.query(`
            SELECT 
                ${selectFields},
                COUNT(*) as total_count,
                SUM(CASE WHEN b.category = 'refund_product' AND b.type = 'weixin_pay' 
                    THEN 1 ELSE 0 END) as weixin_count,
                SUM(CASE WHEN b.category = 'now_money' AND b.type = 'pay_product_refund' 
                    THEN 1 ELSE 0 END) as tupay_count,
                SUM(b.number) as total_amount,
                SUM(CASE WHEN b.category = 'refund_product' AND b.type = 'weixin_pay' 
                    THEN b.number ELSE 0 END) as weixin_amount,
                SUM(CASE WHEN b.category = 'now_money' AND b.type = 'pay_product_refund' 
                    THEN b.number ELSE 0 END) as tupay_amount,
                MIN(b.add_time) as first_refund_time,
                MAX(b.add_time) as last_refund_time
            FROM eb_user_bill b
            LEFT JOIN eb_store_order o ON b.link_id = o.id
            LEFT JOIN eb_user u ON b.uid = u.uid
            LEFT JOIN eb_user u2 ON u.spread_uid = u2.uid
            WHERE ${conditions.join(' AND ')}
            GROUP BY ${groupField}
            ORDER BY total_amount DESC
        `, params);

        // 计算总计
        const totals = stats.reduce((acc, curr) => ({
            total_count: acc.total_count + curr.total_count,
            weixin_count: acc.weixin_count + curr.weixin_count,
            tupay_count: acc.tupay_count + curr.tupay_count,
            total_amount: acc.total_amount + curr.total_amount,
            weixin_amount: acc.weixin_amount + curr.weixin_amount,
            tupay_amount: acc.tupay_amount + curr.tupay_amount
        }), {
            total_count: 0,
            weixin_count: 0,
            tupay_count: 0,
            total_amount: 0,
            weixin_amount: 0,
            tupay_amount: 0
        });

        // 格式化数据
        stats.forEach(row => {
            row.first_refund_time = moment.unix(row.first_refund_time).format('YYYY-MM-DD HH:mm');
            row.last_refund_time = moment.unix(row.last_refund_time).format('YYYY-MM-DD HH:mm');
            row.total_amount = row.total_amount.toFixed(2);
            row.weixin_amount = row.weixin_amount.toFixed(2);
            row.tupay_amount = row.tupay_amount.toFixed(2);
        });

        res.render('refund/stats', {
            title: '退款统计',
            description: '退款数据统计分析',
            stats,
            totals,
            begin,
            end,
            group_by,
            downloadLink: `/apps/admin/refunds/stats/export?begin=${begin}&end=${end}&group_by=${group_by}`
        });
    } catch (error) {
        logger.error('Error generating refund stats:', error);
        res.status(500).render('warn', {
            title: '系统错误',
            message: '生成退款统计失败',
            description: error.message
        });
    }
});

module.exports = router; 
